package ch.hslu.hs12.dmg;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateBestellung{

	private int selector;
	private int selectorTwo;
	private Connection conn;
	private BufferedReader bin = new BufferedReader(new InputStreamReader(
			System.in));

	public CreateBestellung() {
		selector = 0;
		selectorTwo = 0;
		conn = Connector.connect();
	}

	public void createNewBestellung() {

		selector = 0;
		selectorTwo = 0;
		String bikeType = null;
		String size = null;
		String date;
		int qty;
		int kundenNo;
		int bestellTeilNr;

		try {

			System.out.println("Please enter your Kundennummer. ");
			kundenNo = Integer.parseInt(bin.readLine());

			do {
				System.out.println("Which bike would you like to order? \n"
						+ "1: Downhiller \n" + "2: Fully \n" + "3: Hardtail \n"
						+ "4: Fixi ");

				selector = Integer.parseInt(bin.readLine());

				if (!((selector == 1 | selector == 2 | selector == 3 | selector == 4))) {
					System.out
							.println("This machine is not available, please enter again.");
				}

			} while (!(selector == 1 | selector == 2 | selector == 3 | selector == 4));

			switch (selector) {
			case 1:
				bikeType = "Downhiller";
				break;
			case 2:
				bikeType = "Fully";
				break;
			case 3:
				bikeType = "Hardtail";
				break;
			case 4:
				bikeType = "Fixi";
				break;
			default:
				System.out.println("Invalid bike");
			}

			do {
				System.out.println("Which size would you liket o order? \n"
						+ "1: Small \n" + "2: Medium \n" + "3: Large \n");

				selectorTwo = Integer.parseInt(bin.readLine());

				if (!((selectorTwo == 1 | selectorTwo == 2 | selectorTwo == 3 | selectorTwo == 4))) {
					System.out
							.println("This machine is not available, please enter again.");
				}

			} while (!(selectorTwo == 1 | selectorTwo == 2 | selectorTwo == 3 | selectorTwo == 4));

			switch (selectorTwo) {
			case 1:
				size = "Small";
				break;
			case 2:
				size = "Medium";
				break;
			case 3:
				size = "Large";
				break;
			default:
				System.out.println("Invalid size");
			}

			System.out
					.println("Please enter your requested date of delivery (JJJJ-MM-DD: ");
			date = bin.readLine();

			System.out.println("How many bikes would you like to order?: ");
			qty = Integer.parseInt(bin.readLine());

			/*
			 * Description for bike key
			 * 
			 * 11: Downhiller Small 12: Downhiller Medium 13: Downhiller Large
			 * 
			 * 21: Fully Small 22: Fully Medium 23: Fully Large
			 * 
			 * 31: Hardtail Small 32: Hardtail Medium 33: Hardtail Large
			 * 
			 * 41: Fixi Small 42: Fixi Medium 43: Fixi Large
			 */

			// Calculate bike key
			selector = selector * 10;
			selector = selector + selectorTwo;

			Statement myQuery;
			myQuery = conn.createStatement();

			myQuery.executeUpdate("INSERT INTO Teile (Groessenangabe, Preis, Bezeichnung, Typ)"
					+ "VALUES ('"+ size +"', 1000, '" + bikeType + "',"+selector+")");

			conn.commit();
			
			ResultSet rs = myQuery.executeQuery("SELECT *" +
					" FROM Teile" +
					" WHERE Teilnr=(SELECT MAX(Teilnr) FROM Teile)");		
			rs.next();
			bestellTeilNr = rs.getInt("Teilnr");
			rs.close();	
			
			myQuery = conn.createStatement();

			myQuery.executeUpdate("INSERT INTO Kundenbestellung (Kundennummer, Liefertermin, AnzahlTeile, BestellTeilNr)"
					+ "VALUES (" + kundenNo + ",'" + date + "'," + qty + "," + bestellTeilNr + ")");

			conn.commit();
			
			

			System.out.println("\n You ordered following bike: \n"
					+ "Biketype: " + bikeType + "\n" + "Size: " + size + "\n"
					+ "Thank you for your order!");

		} catch (SQLException e) {
			System.out
					.println("SQLException while trying to add a Kundenbestellung");
			e.printStackTrace();
		} catch (IOException e) {
			System.out
					.println("IOException while trying to add a Kundenbestellung");
			e.printStackTrace();
		}

	}

	public void handleBestellungen() {
		
		int teilNo = 0;
		String bez = null;
		int kundenNo = 0;
		String size = null;
		
		Statement myQuery;
		try {
			myQuery = conn.createStatement();


			ResultSet rs = myQuery.executeQuery("SELECT *" +
					"FROM Teile t " +
					"WHERE t.Teilnr IN (SELECT BestellTeilNr From Kundenbestellung) AND t.Typ >= 10000");		
			
			if(rs.next()){
				teilNo = rs.getInt("Teilnr");
				bez = rs.getString("Bezeichnung");
				size = rs.getString("Groessenangabe");		
				
				rs = myQuery.executeQuery("SELECT Kundennummer " +
						"FROM Kundenbestellung x " +
						"WHERE x.BestellTeilNr = "+ teilNo);
				
				if(rs.next()){
					System.out.println("***********************************************\n" +
							"****** Following bike is ready for take off: \n" +
							"****** Bike: "+ bez + "\n"+
							"****** Size: "+ size + "\n" +
							"****** Kundennummer:"+rs.getInt("Kundennummer")+"\n" +
							"***********************************************\n\n");

					rs.close();
					
					myQuery.executeUpdate("DELETE FROM Kundenbestellung " +
							"WHERE BestellTeilNr = " + teilNo); 
					
					myQuery.executeUpdate("DELETE FROM Teile " +
							"WHERE Teilnr = " + teilNo); 				
				}
				else{
					System.out.println("There is no Bestellung to the finished part");
				}
			}
			
			conn.commit();

		} catch (SQLException e) {
			System.out.println("Error while checking for completed orders");
			e.printStackTrace();
		}
	}

}
